package com.zslin.business.dao;

import com.zslin.business.dto.OrdersCountSpecsDto;
import com.zslin.business.model.OrdersProduct;
import com.zslin.core.repository.BaseRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * Created by 钟述林 on 2019-12-18.
 */
public interface IOrdersProductDao extends BaseRepository<OrdersProduct, Integer>, JpaSpecificationExecutor<OrdersProduct> {

    List<OrdersProduct> findByOrdersId(Integer ordersId);

    List<OrdersProduct> findByOrdersNo(String ordersNo);

    @Query("UPDATE OrdersProduct o SET o.status=?1 WHERE o.ordersNo=?2 ")
    @Modifying
    @Transactional
    void updateStatus(String status, String ordersNo);

    @Query("UPDATE OrdersProduct o SET o.status='1', o.payDay=?1, o.payTime=?2, o.payLong=?3 WHERE o.ordersNo=?4")
    @Modifying
    @Transactional
    void updatePayDay(String payDay, String payTime, Long payLong, String ordersNo);

    @Query("UPDATE OrdersProduct o SET o.status=?1 WHERE o.id=?2 ")
    @Modifying
    @Transactional
    void updateStatusByProId(String status, Integer ordersProId);

    @Query("SELECT new com.zslin.business.dto.OrdersCountSpecsDto(o.proTitle, o.specsId, o.specsName, SUM(o.amount), o.payDay, SUM(o.backMoney), SUM(o.price*o.amount), SUM(o.autoCommissionMoney), SUM(o.price*o.amount)-SUM(o.backMoney)-SUM(o.autoCommissionMoney)) FROM OrdersProduct o WHERE o.payDay=?1 GROUP BY o.specsId")
    List<OrdersCountSpecsDto> findDtoByDay(String day);

    @Query("SELECT new com.zslin.business.dto.OrdersCountSpecsDto(o.proTitle, o.specsId, o.specsName, SUM(o.amount), o.payDay, SUM(o.backMoney), SUM(o.price*o.amount), SUM(o.autoCommissionMoney), SUM(o.price*o.amount)-SUM(o.backMoney)-SUM(o.autoCommissionMoney)) FROM OrdersProduct o WHERE o.payDay LIKE ?1% GROUP BY o.specsId")
    List<OrdersCountSpecsDto> findDtoByMonth(String month);

    @Query("FROM OrdersProduct o WHERE o.payDay=?1 AND o.specsId=?2")
    List<OrdersProduct> findByDay(String day, Integer specsId);

    @Query("FROM OrdersProduct o WHERE o.payDay LIKE ?1% AND o.specsId=?2")
    List<OrdersProduct> findByMonth(String day, Integer specsId);
}
